﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;
using System.Data;
using System.Text;

namespace DVSProject.Lib
{
    public class ReportRepo
    {
        public const string m_msg_head = "LocationMachineSet";

        //public SummaryMeterModels GetLocationMachineSetForEditById(string id)
        //{
        //    SummaryMeterModels SummaryMeter = new SummaryMeterModels();
        //    string SQLCommand = @"select * from TBLocationMachineSet where code_no=@code_no";

        //    using (var db = new DBHelper())
        //    {
        //        try
        //        {
        //            db.AddParameter("@code_no", id.Trim());
        //            var reader = db.ExecuteReader(SQLCommand);
        //            if (reader.Read())
        //            {
        //                LocationMachineSet.code_no = SF.GetString(reader["code_no"].ToString().Trim());
        //                LocationMachineSet.LocationSetCode = SF.GetString(reader["LocationSetCode"].ToString().Trim());
        //                LocationMachineSet.SerialNo = (string)reader["SerialNo"];
        //                LocationMachineSet.start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
        //                LocationMachineSet.last_meter_no = (reader["last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["last_meter_no"]) : 0;
        //                LocationMachineSet.total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
        //                LocationMachineSet.contract_start_date = (DateTime)reader["contract_start_date"];// ConvertMMddYYYY(LocationMachineSet.contract_start_date.ToString()));

        //                LocationMachineSet.remark = (!string.IsNullOrEmpty(reader["remark"].ToString())) ? (string)reader["remark"] : "";
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
        //        }
        //    }
        //    return LocationMachineSet;
        //}

        public IEnumerable<ProductTypeModels> GetProductType()
        {
            List<ProductTypeModels> ProductTypes = new List<ProductTypeModels>();
            string SQLCommand = @"SELECT NameTh FROM dvsuser.TBMachine
                                  GROUP BY NameTh";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        ProductTypeModels ProductType = new ProductTypeModels();
                        ProductType.NameThValue = (string)reader["NameTh"];
                        ProductType.NameThText = (string)reader["NameTh"];

                        ProductTypes.Add(ProductType);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return ProductTypes.ToList();
        }

//        public IEnumerable<LocationSetModels> GetLocationSetForReport()
//        {
//            List<LocationSetModels> LocationSets = new List<LocationSetModels>();
//            string SQLCommand = @"select TBLocationSet.*,TBMerchant.NameTh AS Merchant_NameEn from TBLocationSet LEFT JOIN TBMerchant
//                                  ON TBLocationSet.MerchantId = TBMerchant.MerchantId WHERE TBLocationSet.Status <> 'Delete'";
//            using (var db = new DBHelper())
//            {
//                try
//                {
//                    var reader = db.ExecuteReader(SQLCommand);
//                    int i = 0;

//                    LocationSetModels LocationSet = new LocationSetModels();
//                    LocationSet.RowCount = i;
//                    LocationSet.LocSetId = (int)reader["LocSetId"];
//                    LocationSet.LocationSetCode = (string)reader["LocationSetCode"];
//                    LocationSet.MerchantId = (int)reader["MerchantId"];
//                    LocationSet.Merchant_NameEn = (string)reader["Merchant_NameEn"];
//                    LocationSet.LocSetNameTh = (string)reader["LocSetNameTh"];
//                    LocationSet.LocSetNameEng = (string)reader["LocSetNameEng"];
//                    LocationSet.Status = (string)reader["Status"];
//                    LocationSet.CreateBy = (string)reader["CreateBy"];
//                    LocationSet.CreateOn = (DateTime)reader["CreateOn"];
//                    LocationSet.UpdateBy = (string)reader["UpdateBy"];
//                    LocationSet.UpdateOn = (DateTime)reader["UpdateOn"];

//                    LocationSets.Add(LocationSet);
//                    while (reader.Read())
//                    {
//                        i = i + 1;
//                        LocationSetModels LocationSet = new LocationSetModels();
//                        LocationSet.RowCount = i;
//                        LocationSet.LocSetId = (int)reader["LocSetId"];
//                        LocationSet.LocationSetCode = (string)reader["LocationSetCode"];
//                        LocationSet.MerchantId = (int)reader["MerchantId"];
//                        LocationSet.Merchant_NameEn = (string)reader["Merchant_NameEn"];
//                        LocationSet.LocSetNameTh = (string)reader["LocSetNameTh"];
//                        LocationSet.LocSetNameEng = (string)reader["LocSetNameEng"];
//                        LocationSet.Status = (string)reader["Status"];
//                        LocationSet.CreateBy = (string)reader["CreateBy"];
//                        LocationSet.CreateOn = (DateTime)reader["CreateOn"];
//                        LocationSet.UpdateBy = (string)reader["UpdateBy"];
//                        LocationSet.UpdateOn = (DateTime)reader["UpdateOn"];

//                        LocationSets.Add(LocationSet);
//                    }
//                }
//                catch (Exception ex)
//                {
//                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
//                }
//            }
//            return LocationSets.ToList();
//        }

        public IEnumerable<MonthModels> GetMonthAll()
        {
            List<MonthModels> Months = new List<MonthModels>();

            MonthList_THA w = MonthList_THA.มกราคม;
            foreach (int i in Enum.GetValues(w.GetType()))
            {
                MonthModels _Month = new MonthModels();
                _Month.month_id = i;
                _Month.month_name = Enum.GetName(w.GetType(), i);
                Months.Add(_Month);

            }
            return Months.ToList();
        }

        public DataTable GetSummaryMeterByRangeDate(string ProductName, string start_date, string end_date)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @"SELECT * FROM VVisitList
                                  WHERE MachineNameTh = @MachineNameTh AND DateTimeOut BETWEEN @DateTimeOut1 AND @DateTimeOut2";
            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@DateTimeOut1", start_date + " 00:00:00");
                    db.AddParameter("@DateTimeOut2", end_date + " 23:59:59");
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                    //while (reader.Read())
                    //{
                    //    SummaryMeterModels SummaryMeter = new SummaryMeterModels();

                    //    SummaryMeter.FSR_NO = (string)reader["FSR_NO"];
                    //    SummaryMeter.LocationSetCode = (string)reader["LocationSetCode"];
                    //    SummaryMeter.BranchName = (string)reader["BranchName"];
                    //    SummaryMeter.SerialNo = (string)reader["SerialNo"];
                    //    SummaryMeter.MachineNameTh = (string)reader["MachineNameTh"];
                    //    SummaryMeter.ModelId = (int)reader["ModelId"];
                    //    SummaryMeter.ModelName = (string)reader["ModelName"];

                    //    SummaryMeter.PreviousCount = (reader["PreviousCount"] != DBNull.Value) ? SF.GetDouble(reader["PreviousCount"]) : 0;
                    //    SummaryMeter.PresentCount = (reader["PresentCount"] != DBNull.Value) ? SF.GetDouble(reader["PresentCount"]) : 0;
                    //    SummaryMeter.TotalCount = (reader["TotalCount"] != DBNull.Value) ? SF.GetDouble(reader["TotalCount"]) : 0;
                    //    SummaryMeter.ConversionRate = (int)reader["ConversionRate"];
                    //    SummaryMeter.TotalNoOfRacks = (reader["TotalNoOfRacks"] != DBNull.Value) ? SF.GetDouble(reader["TotalNoOfRacks"]) : 0;
                    //    SummaryMeter.CalPreviousMeter = (reader["CalPreviousMeter"] != DBNull.Value) ? SF.GetDouble(reader["CalPreviousMeter"]) : 0;
                    //    SummaryMeter.CalPresentMeter = (reader["CalPresentMeter"] != DBNull.Value) ? SF.GetDouble(reader["CalPresentMeter"]) : 0;

                    //    SummaryMeter.Rent = (reader["Rent"] != DBNull.Value) ? (decimal)reader["Rent"] : 0;
                    //    SummaryMeter.Discount = (reader["Discount"] != DBNull.Value) ? (int)reader["Discount"] : 0;
                    //    SummaryMeter.DateTimeOut = (DateTime)reader["DateTimeOut"];
                    //    SummaryMeter.WorkType = (string)reader["WorkType"];

                    //    SummaryMeters.Add(SummaryMeter);
                    //}
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        //new
        public IEnumerable<SummaryMeterModels> GetSummaryMeterByRangeDateDtl(string ProductName, string start_date, string end_date)
        {
           // DataSet SummaryMeters = new DataSet();
            string SQLCommand = @"SELECT * FROM VVisitList
                                  WHERE MachineNameTh = @MachineNameTh AND DateTimeOut BETWEEN @DateTimeOut1 AND @DateTimeOut2";

            List<SummaryMeterModels> SummaryMeters = new List<SummaryMeterModels>();
            double summaryTotal = 0;
            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@DateTimeOut1", start_date + " 00:00:00");
                    db.AddParameter("@DateTimeOut2", end_date + " 23:59:59");
                    //SummaryMeters = db.ExecuteDataSet(SQLCommand);
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        SummaryMeterModels SummaryMeter = new SummaryMeterModels();

                        double _HT055 =0;
                        double _percentDiscount = 0;
                        double _percentPer100 = 0;
                        double _rent = 0;
                        double _HT055AndRent = 0;

                        SummaryMeter.FSR_NO = (string)reader["FSR_NO"];
                        SummaryMeter.LocationSetCode = (string)reader["LocationSetCode"];
                        SummaryMeter.BranchName = (string)reader["BranchName"];
                        SummaryMeter.SerialNo = (string)reader["SerialNo"];
                        SummaryMeter.MachineNameTh = (string)reader["MachineNameTh"];
                        SummaryMeter.ModelId = (int)reader["ModelId"];
                        SummaryMeter.ModelName = (string)reader["ModelName"];

                        SummaryMeter.PreviousCount = (reader["PreviousCount"] != DBNull.Value) ? SF.GetDouble(reader["PreviousCount"]) : 0;
                        SummaryMeter.PresentCount = (reader["PresentCount"] != DBNull.Value) ? SF.GetDouble(reader["PresentCount"]) : 0;
                        SummaryMeter.TotalCount = (reader["TotalCount"] != DBNull.Value) ? SF.GetDouble(reader["TotalCount"]) : 0;
                        SummaryMeter.ConversionRate = (int)reader["ConversionRate"];
                        SummaryMeter.TotalNoOfRacks = (reader["TotalNoOfRacks"] != DBNull.Value) ? SF.GetDouble(reader["TotalNoOfRacks"]) : 0;
                        SummaryMeter.CalPreviousMeter = (reader["CalPreviousMeter"] != DBNull.Value) ? SF.GetDouble(reader["CalPreviousMeter"]) : 0;
                        SummaryMeter.CalPresentMeter = (reader["CalPresentMeter"] != DBNull.Value) ? SF.GetDouble(reader["CalPresentMeter"]) : 0;
                       
                        SummaryMeter.Rent = (reader["Rent"] != DBNull.Value) ? (decimal)reader["Rent"] : 0;
                        SummaryMeter.Discount = (reader["Discount"] != DBNull.Value) ? (int)reader["Discount"] : 0;
                        SummaryMeter.DateTimeOut = (DateTime)reader["DateTimeOut"];
                        SummaryMeter.WorkType = (string)reader["WorkType"];

                        _HT055 = SummaryMeter.TotalNoOfRacks * 0.55;
                        _percentPer100 = (SummaryMeter.Discount * 0.01);
                        _rent = SF.GetDouble(SummaryMeter.Rent);// (reader["Rent"] != DBNull.Value) ? (double)reader["Rent"] : 0;

                        _percentDiscount = _rent - (_rent * _percentPer100);
                        _HT055AndRent = (_rent - (_rent * _percentPer100)) + _HT055;

                        summaryTotal = summaryTotal + _HT055AndRent;
                        SummaryMeter.HT055 = _HT055;
                        SummaryMeter.caldiscount_rent = _percentDiscount;
                        SummaryMeter.HT055AndRent = _HT055AndRent;
                        SummaryMeter.summaryTotal = summaryTotal;
                        SummaryMeters.Add(SummaryMeter);
                    }
                  

                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
                
            }
            return SummaryMeters;//.Tables[0];
        }
        public DataTable GetComplainByRangeDate( string start_date, string end_date)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" SELECT TBComplain.*,TBLocationSet.LocSetNameTh AS LocationName,'เครื่องล้างจาน/เครื่องล้างแก้ว' AS TypeService, 'บริษัทไดเวอร์ซี่ไฮยีน(ประเทศไทย)จำกัด' AS Diversey,isnull(TBEmployee.NameTh,'') AS EmpName
                                    FROM TBComplain LEFT JOIN TBLocationSet 
                                    ON TBComplain.LocationSetCode = TBLocationSet.LocationSetCode
                                    LEFT JOIN  TBEmployee ON TBComplain.EmpId = TBEmployee.EmpId
                                    WHERE TBLocationSet.Status = 'Active' AND ComplainDate BETWEEN @DateTimeOut1 AND @DateTimeOut2";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@DateTimeOut1", start_date+" 00:00:00");
                    db.AddParameter("@DateTimeOut2", end_date + " 23:59:59");
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitHD_EXEC(string FSR_NO)
        {
            const string query = "SP_GETVISIT_HD_BY_FSR_NO";
            using (var db = new DBHelper())
            {

                db.AddParameter("@FSR_NO", FSR_NO);
                var dt = db.ExecuteDataSet(query, CommandType.StoredProcedure).Tables[0];
                return dt;
            }
        }



        public DataTable VisitHD(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" SELECT VHD.FSR_NO,VHD.LocationSetCode,L.LocSetNameTh,VHD.SignBy,VHD.SignDateTime,VHD.DateTimeIn,VHD.DateTimeOut,
                                          VHD.WorkType,VHD.Presentcount,VHD.PreviousCount,VHD.TotalCount,VHD.ConversionRate,VHD.TotalNoOfRacks,
                                          VHD.Rent,VHD.Discount,VHD.ServiceBy,E.NameTh AS ServiceName,VHD.CreateOn,VHD.Status,VHD.IsComplain,
                                          VHD.SerialNo,MM.NameTh AS ModelName,VHD.Comment
                                          --(SELECT SUM(Point) FROM TBVisitDtlWashFactor WHERE FSR_NO = @FSR_NO AND Actual ='NA') AS TotalPoint
                                   FROM TBVisitHD VHD LEFT JOIN TBLocationSet L ON VHD.LocationSetCode = L.LocationSetCode
                                                      LEFT JOIN TBEmployee E ON VHD.ServiceBy = E.EmpId
                                                      INNER JOIN VModelMachine MM ON VHD.SerialNo = MM.SerialNo
                                   WHERE VHD.SerialNo <> '' AND VHD.FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }
        private string ConvertMMddYYYY(string date)
        {
            date = date.Substring(0, 10);
            string[] splitdate = date.Split('-');
            //Int32 y = Convert.ToInt32(splitdate[2]);
            date = splitdate[1] + "/" + splitdate[0] + "/" + splitdate[2];// Convert.ToString(2);
            return date;
        }

        public IEnumerable<AccessModels> GetVisitReport(string location,string startDate,string endDate,string FSN_NO,string CreateBy)
        {

            string sqlWhere = "";
            if (FSN_NO != "")
            {
                sqlWhere = " And TBVisitHD.FSR_NO ='" + FSN_NO + "'";

            }

            if (location!="")
            {
                sqlWhere += " And TBVisitHD.LocationSetCode ='" + location + "'";
            }

            if (startDate != "" && endDate !="")
            {
                sqlWhere += " And TBVisitHD.DateTimeIn >='" + ConvertMMddYYYY(startDate) +  " 0:00 ' And TBVisitHD.DateTimeIn <='" + ConvertMMddYYYY(endDate) + " 23:59'";
            }

            if (CreateBy != "")
            {
                sqlWhere += " And TBVisitHD.CreateBy ='" + CreateBy + "'";
            }
            

            List<AccessModels> accesss =new List<AccessModels>();
              string SQLCommand  = string.Format(@"
					 select TOP 20 *, TBVisitHD.LocationSetCode +'-'+TBLocationSet.LocSetNameTh AS locationSetCodeFullName,
                                         isnull(TBVisitHD.SerialNo,'')+'<'+isnull(TBModel.NameTh,'') +'>'+ isnull(TBMachine.NameTh,'') AS MachineFullName
                                  from TBVisitHD LEFT JOIN TBLocationSet
                                          ON TBVisitHD.LocationSetCode = TBLocationSet.LocationSetCode LEFT JOIN TBMachine
                                          ON TBVisitHD.SerialNo = TBMachine.SerialNo LEFT JOIN TBModel 
                                          ON TBMachine.ModelId = TBModel.ModelId
                                 WHERE TBVisitHD.Status <> 'Delete'  {0}
                                 
                                 ORDER BY DateTimeIn DESC ", sqlWhere);

//            string SQLCommand = @"   select TOP 20 *, TBVisitHD.LocationSetCode +'-'+TBLocationSet.LocSetNameTh AS locationSetCodeFullName,
//                                         isnull(TBVisitHD.SerialNo,'')+'<'+isnull(TBModel.NameTh,'') +'>'+ isnull(TBMachine.NameTh,'') AS MachineFullName
//                                  from TBVisitHD LEFT JOIN TBLocationSet
//                                          ON TBVisitHD.LocationSetCode = TBLocationSet.LocationSetCode LEFT JOIN TBMachine
//                                          ON TBVisitHD.SerialNo = TBMachine.SerialNo LEFT JOIN TBModel 
//                                          ON TBMachine.ModelId = TBModel.ModelId
//                                 WHERE TBVisitHD.Status <> 'Delete'  
//                                 
//                                 ORDER BY DateTimeIn DESC";
            //And TBVisitHD.CreateBy=@UserId";
            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@sqlWhere", sqlWhere);
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        AccessModels access = new AccessModels();
                         

                        access.FSR_NO = (string)reader["FSR_NO"];
                        access.LocationSetCode = (string)reader["LocationSetCode"];
                        access.LocationSetCodeFullName = (string)reader["LocationSetCodeFullName"];
                        access.SerialNo = (string)reader["SerialNo"];
                        access.MachineFullName = (string)reader["MachineFullName"];
                        access.DateTimeIn = (DateTime)reader["DateTimeIn"];
                        access.DateTimeOut = (DateTime)reader["DateTimeOut"];
                        access.SignDatetime = (DateTime)reader["SignDatetime"];
                        access.WorkType = (string)reader["WorkType"];
                        access.SignBy = (string)reader["SignBy"];
                        access.Status = (string)reader["Status"];
                        access.IsComplain = (bool)reader["IsComplain"];// == '1' ? true : false;

                        accesss.Add(access);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return accesss.ToList();
        }

      
       

        public DataTable VisitDtlResult(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" SELECT FSR_NO,seq,TitleResultName,SoilRemovalBit,FilmStreakBit,SpottedBit,ResultRemarkTh,ResultRemarkEng,Point,
                                          (SELECT SUM(Point) FROM TBVisitDtlResult WHERE FSR_NO = @FSR_NO AND ResultRemarkTh <> 'NA') AS SumPoint,
                                          (SELECT SUM(TBTitleResult.stdpoint) FROM TBVisitDtlResult LEFT JOIN TBTitleResult 
                                                    ON TBVisitDtlResult.seq = TBTitleResult.TitleResultId 
                                                    WHERE TBVisitDtlResult.FSR_NO = @FSR_NO AND TBVisitDtlResult.ResultRemarkTh <>'NA') AS stdpoint
                                   FROM TBVisitDtlResult WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitDtlProcedure(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" SELECT *,(SELECT SUM(Point) FROM TBVisitDtlProcedure WHERE FSR_NO =  @FSR_NO) AS SumPoint,
                                     (SELECT SUM(Point) FROM TBVisitDtlProcedure WHERE FSR_NO =  @FSR_NO) AS stdpoint
                                    FROM TBVisitDtlProcedure
                                   WHERE FSR_NO =  @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitDtlWashFactor(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @"  SELECT TBVisitDtlWashFactor.*,(SELECT SUM(TBVisitDtlWashFactor.Point) FROM TBVisitDtlWashFactor WHERE TBVisitDtlWashFactor.FSR_NO = @FSR_NO) AS SumPoint,
                                          (SELECT SUM(TBTitleWashFactors.stdpoint) FROM TBVisitDtlWashFactor LEFT JOIN TBTitleWashFactors 
                                                    ON TBVisitDtlWashFactor.seq = TBTitleWashFactors.TitleWashFactorsId 
                                                    WHERE TBVisitDtlWashFactor.FSR_NO = @FSR_NO AND TBVisitDtlWashFactor.Actual <>'NA') AS stdpoint
                                    FROM TBVisitDtlWashFactor
                                    WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitDtlMechanical(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @"  SELECT *,(SELECT SUM(Point) FROM TBVisitDtlMechanical WHERE FSR_NO = @FSR_NO) AS SumPoint,
                                             (SELECT SUM(TBTitleMechanical.stdpoint) FROM TBVisitDtlMechanical LEFT JOIN TBTitleMechanical 
                                                      ON TBVisitDtlMechanical.seq = TBTitleMechanical.TitleMechanicalId 
                                                      WHERE TBVisitDtlMechanical.FSR_NO = @FSR_NO AND TBVisitDtlMechanical.MechanicalRemarkTh <>'NA') AS stdpoint
                                    FROM TBVisitDtlMechanical
                                    WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0006", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitDtlMwwDispenser(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @"  SELECT *,(SELECT SUM(Point) FROM TBVisitDtlMwwDispenser WHERE FSR_NO =@FSR_NO) AS SumPoint,
                                             3 AS stdpoint
                                    FROM TBVisitDtlMwwDispenser
                                    WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0006", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitDtlFeeder(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @"  SELECT *,(SELECT SUM(Point) FROM TBVisitDtlFeeder WHERE FSR_NO = @FSR_NO  AND TBVisitDtlFeeder.FeederRemarkTh <>'NA') AS SumPoint,
                                     (SELECT SUM(TBVisitDtlFeeder.Point) FROM TBVisitDtlFeeder LEFT JOIN TBTitleFeeDer 
                                         ON TBVisitDtlFeeder.TitleFeeDerName = TBTitleFeeDer.TitleFeeDerName 
                                            WHERE TBVisitDtlFeeder.FSR_NO = @FSR_NO AND TBVisitDtlFeeder.FeederRemarkTh <>'NA') AS stdpoint
                                    FROM TBVisitDtlFeeder
                                    WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0006", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }
        //VisitDtlTraining
        public DataTable VisitDtlTraining(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" SELECT * FROM TBVisitDtlTraining
                                   WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0006", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitDtlHandSoapDispenser(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @"  SELECT *,(SELECT SUM(Point) FROM TBVisitDtlHandSoapDispenser WHERE FSR_NO = @FSR_NO) AS SumPoint,
                                    1 as stdpoint
                                    FROM TBVisitDtlHandSoapDispenser
                                    WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0006", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }

        public DataTable VisitDtlServiceSummary(string FSN_NO)
        {
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" SELECT *,(SELECT SUM(std) FROM TBVisitDtlServiceSummary WHERE FSR_NO = @FSR_NO) AS Sumstd,
                                            (SELECT SUM(Actual) FROM TBVisitDtlServiceSummary WHERE FSR_NO = @FSR_NO) AS SumActual,
                                            (SELECT SUM(CalPercent) FROM TBVisitDtlServiceSummary WHERE FSR_NO = @FSR_NO) AS SumCalPercent
                                   FROM TBVisitDtlServiceSummary
                                   WHERE FSR_NO = @FSR_NO";
            using (var db = new DBHelper())
            {
                try
                {
                    //db.AddParameter("@MachineNameTh", ProductName.Trim());
                    db.AddParameter("@FSR_NO", FSN_NO);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0006", ex));
                }
            }
            return SummaryMeters.Tables[0];
        }
    



    }
}